<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/6/9 0009
 * Time: 上午 9:54
 */

namespace DataAPI\Model;


import("Org.Util.PHPExcel");
import("Org.Util.PHPExcel.Reader.Excel2007");
import("Org.Util.PHPExcel.Reader.Excel5");
import('ORG.Util.PHPExcel.IOFactory');
use DataAPI\Model\SupplierProductsModel;
use Think\Upload;
class ExcelModel
{
    public $table;
    public $batch;
    public $filePath;
    public $PHPExcelReader;
    function __construct()
    {
        $this->table = M('test_supplier_products');
        $this->batch = M('sync_excel');
    }
    public function readExcel($filePath,$bath){
        $res =  $this->ExcelReader($filePath);
        $this->loadDB($res,$bath);
//       if($res == true)
//       {
//           $this->loadDB($this->PHPExcelReader);
//       }
    }

    /**
     * @param $filePath
     * @return \PHPExcel|string
     * 打开Excel 并加载数据 返回一个ExcelReader 对象
     */
    public function ExcelReader($filePath){
        $this->filePath = $filePath;
        $type = strtolower(pathinfo($filePath,PATHINFO_EXTENSION));
        if (!file_exists($filePath)) {
            die('no file!');
        }
        if( $type == 'xlsx'||$type == 'xls' ){
            $PHPReader = new \PHPExcel_Reader_Excel2007(); //默认是excel2007
            //判断读取是否成功不成功则实例化2005的
            if (!$PHPReader->canRead($filePath)) {
                $PHPReader = new \PHPExcel_Reader_Excel5(); //如果不成功的时候用以前的版本来读取
                if (!$PHPReader->canRead($filePath)) {
                    $msg = '请选择需要导入的商品execl文件!';
                    return $msg;
                }
            }
            //加载数据
            return  \PHPExcel_IOFactory::load($filePath);
        }elseif( $type == 'csv' ){
            $objReader = \PHPExcel_IOFactory::createReader('CSV');
            if($objReader->canRead($filePath)){
                $objReader->setDelimiter(',')
                    ->setInputEncoding('GBK')
                    ->setEnclosure('"')
                    ->setLineEnding("\r\n")
                    ->setSheetIndex(0);
                return $objReader->load($filePath);
            }else{
                unlink($filePath);
                die('不知此的文件类型,请重新上传');
            }
        }else{
            unlink($filePath);
            die('不知此的文件类型,请重新上传');
        }
    }

    /**
     * @param $objPHPExcel
     * 处理Excel数据 并添加到数据库
     */
    public function loadDB($objPHPExcel,$batch){
        $validate = new SupplierProductsModel();
        $arr = [];
        $sheetCount=$objPHPExcel->getSheetCount();//获取excel文件里有多少个sheet
        for($i=0;$i<$sheetCount;++$i){
            $arr[$i] = $objPHPExcel->getSheet($i)->toArray();//读取每个sheet里的数据 全部放入到数组中
            for($o=0;$o<count($arr[$i][0]);$o++){
                $column = $arr[$i][0][$o];
                $key = $this->getKey();
                $res = in_array($column,$key);
                if($res === false)
                {
                    echo '工作表'.($i+1).'字段'.$arr[$i][0][$o].'不合法请修改<hr>';
                    unset($this->filePath);
                    exit;
                }
            }
            unset($arr[$i][0]);
        }
        //添加批次
        $this->batch->add(['batch'=>$batch,'is_sync'=>'0','createdtime'=>date("Y-m-d H:i:S",time())]);
        foreach ($arr as $k=>$v){
            foreach ($v as $o=>$item){
                $data['app_id'] = session('app_id');
                $data['earn13'] =$item[0];
                $data['name']=$item[1];
                $data['info_keywords']=isset($item[2])?substr($item[2],0,10):'';
                $data['weight']=$item[3];
                $data['pom']=$item[4];
                $data['info_pc']=$item[5];
                $data['info_mobile']=$item[6];
                $data['market_price']=$item[7];
                $data['acting_price']=$item[8];
                $data['wholesale_price']=$item[9];
                $data['lock_price']=$item[10];
                $data['wholesale_start_num']=$item[11];
                $data['lock_start_num']=$item[12];
                $data['price']=$item[13];
                $data['stock_num']=$item[14];
                $data['warehouse_id']=$item[15];
                $data['b_id']=$item[16];
                $data['type'] = '0';//Excel处理 默认为字符串 '0'
                $data['batch']= $batch;
                $res =$this->table->add($data);
                $validate->getError();
            }
        }
    }
    public function getKey(){
        return [
            'earn13' =>'EN13码',
            'name'=>'商品名称',
            'info_keywords'=>'商品关键字',
            'weight'=>'商品重量',
            'pom'=>'计量单位',
            'info_pc'=>'PC端图片',
            'info_mobile'=>'手机端图片',
            'market_price'=>'市场价',
            'acting_price'=>'一键代发价',
            'wholesale_price'=>'批发价',
            'lock_price'=>'锁库价',
            'wholesale_start_num'=>'最低批发数',
            'lock_start_num'=>'最低锁库数',
            'price'=>'商品原价',
            'stock_num'=>'库存',
            'warehouse_id'=>'仓库ID',
            'b_id'=>'B仓商品编号'
        ];
    }
}